package itcast.dao.impl;

import itcast.dao.OuttDao;
import itcast.domain.Drom;
import itcast.domain.Outt;
import itcast.domain.Studnet;
import itcast.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class OuttDaoImpl implements OuttDao {
    /**
     * 申明JdbcTemplate类,出入连接池
     */
    private JdbcTemplate template=new JdbcTemplate(JDBCUtils.getDataSource());

    //找到迁入学生信息

    @Override
    public List<Outt> findout_student() {
        //定义sql
        String sql="SELECT student_username,student_name,student_sex, Build_Name,Drom_Name, Student_state FROM student,drom,build WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and student_state='已迁入'";

        List<Outt>  outts=template.query(sql,new BeanPropertyRowMapper<Outt>(Outt.class));

        return outts;
    }

    @Override
    public List<Outt> findout_studentbyDate(Map<String, String[]> condition) {
        String sql="SELECT student_username,student_name,student_sex, Build_Name,Drom_Name, Student_state FROM student,drom,build WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and student_state='已迁入' and 1=1";
        StringBuilder sb=new StringBuilder(sql);
        //2.遍历map中value是否有值
        Set<String> keySet =condition.keySet();
        //定义参数集合
        List<Object> params=new ArrayList<Object>();
        for(String key :keySet){
            //获取value
            String value=condition.get(key)[0];
            //判断value是否有值
            if(value!=null && !"".equals(value)){
                sb.append(" and "+key+" = ? ");
                params.add(value);//条件的值
            }
        }
        System.out.println(sb.toString());
        System.out.println(params);
        List<Outt> outts=template.query(sb.toString(),new BeanPropertyRowMapper<Outt>(Outt.class),params.toArray());
        return outts;
    }

    @Override
    public Outt findout_studentbyid(String username) {
        String sql="SELECT student_id, student_username,student_name,student_sex, Build_Name,Drom_Name, Student_state FROM student,drom,build WHERE student.Student_DomitoryID=drom.Drom_ID and drom.Drom_BuildID=build.Build_ID and 1=1 and student_username=?";
        return template.queryForObject(sql,new BeanPropertyRowMapper<Outt>(Outt.class),username);
    }

    //设置迁出信息学生

    @Override
    public void updateout_student(Outt outt) {
        String sql1="INSERT INTO outt (out_studentid,out_date,out_remark) VALUES(?,?,?);";
        String sql=" update student,drom SET  student.student_domitoryid=null,student.student_state='已迁出',drom.drom_number=drom.drom_number-1 where  student.Student_DomitoryID=drom.Drom_ID  AND 1=1 and Student_userName=?";

        //执行sql语句
        template.update(sql1,outt.getStudent_id(), outt.getOut_date(),outt.getOut_remark());
        template.update(sql,outt.getStudent_username());


    }
    //查找迁出记录

    @Override
    public List<Outt> findout_remeber() {
        String sql="select out_date,student_name,student_sex,student_class,out_remark from student,outt where outt.Out_StudentID=student.Student_ID  AND 1=1 and student_state='已迁出'";
        List<Outt>  outts=template.query(sql,new BeanPropertyRowMapper<Outt>(Outt.class));

        return outts;
    }
}
